# 按月查询，每个excel一个校区、一个月的数据
# excel数据入库
import os
import re
import math

from TestModel.utils.excel import readByColumn
from TestModel.models import BizhichuData
from TestModel.models import Bizhichu


class Main:
    # 读取文件名
    def readDir(slef, dirName):
        files = []
        for fileName in os.listdir(dirName):
            filePath = os.path.join(dirName, fileName)
            files.append(filePath)
            print(filePath)
        return files


    def parse_filename(self, filename) -> BizhichuData:
        """
        解析文件名字符串，提取 year, month 和 schoolName。

        :param filename: 文件名字符串
        :return: 包含 year, month, schoolName 的字典
        """
        data = BizhichuData()
        pattern = r"(\d{4})年(\d{1,2})月.*?版本\((.*?)\)\.xlsx"
        match = re.search(pattern, filename)
        if match:
            data.year = int(match.group(1))
            data.month = int(match.group(2))
            data.school_name = match.group(3)
            print(data.year)
            print(data.month)
            print(data.school_name)
            return data

        else:
            raise ValueError("文件名格式不符合预期")

    def dumpDataKey(self):
        biZhichus = Bizhichu.objects.all()
        return biZhichus

    def main(self):
        biZhichus = self.dumpDataKey()
        files = self.readDir('F:\项目\BI\月份数据')
        for file in files:
            # data = BizhichuData()
            data = self.parse_filename(file)
            columnData = readByColumn(file, 8)
            i = 3
            for biZhichu in biZhichus:
                id = biZhichu.id
                value = columnData[i]
                new_data = BizhichuData(  # 替换为实际的模型名称
                    fk_id=id,
                    year=data.year,  # 使用解析得到的年
                    month=data.month,  # 使用解析得到的月
                    school_name=data.school_name,  # 使用解析得到的校区名称
                    value=0 if math.isnan(value) else value,
                )
                new_data.save()
                i = i + 1

# if __name__ == '__main__':
#     # readDir('F:\项目\BI\月份数据')
#     parse_filename('2024年7月学校主体月度预算表_实际数_人民币_默认版本(丘北校区).xlsx')
